﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

namespace LUStationery.DAO
{
    public class ItemDAO
    {

        SqlConnection cn;
        SqlCommand cmd;


        public ItemDAO()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
           
        }
        public IDataReader getItems()
        {
            cmd = new SqlCommand("Select * from Item", cn);
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getItemByCategory(string category)
        {
            
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from item where category=@category";
            cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value=category;
            cn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
        }

        // ZHONG YUE \\
        public IDataReader getUnSafeStockItems()
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from item where balance < reorder_level";
            cn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);


        }
        // ZHONG YUE \\
        public IDataReader getItemsBySupplierAndCategory(string supplier, string category)
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from item where category=@Category and item_code in (select item_code from supplier_item where supplier_name=@Supplier)";
            cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = category;
            cmd.Parameters.Add("@Supplier", SqlDbType.VarChar).Value = supplier;
            cn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        // ZHONG YUE \\
        public IDataReader getItemsByCategory(string category)
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from item where category=@Category";
            cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = category;
            cn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        // ZHONG YUE \\
        public IDataReader getItemByItemCode(string itemCode)
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select * from item where item_code=@itemCode";
            cmd.Parameters.Add("@itemCode", SqlDbType.VarChar).Value = itemCode;
            cn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

    }
}